package aos.ydksxt.service.cxbdfx;

import aos.framework.core.typewrap.Dto;
import aos.framework.core.typewrap.Dtos;
import aos.framework.core.utils.AOSJson;
import aos.framework.core.utils.AOSUtils;
import aos.framework.web.router.HttpModel;
import aos.ydksxt.service.common.CommonService;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.CellCopyPolicy;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder;
import org.springframework.stereotype.Service;

import javax.transaction.Transactional;
import java.awt.*;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 国土采集信息比对分析服务
 * Created by Stiofan on 2017/3/27.
 */
@Service
public class GtcjxxbdfxService extends CommonService {
    @Override
    @Transactional
    public void initView(HttpModel httpModel) {
        httpModel.setAttribute("juid", httpModel.getInDto().get("juid"));
        httpModel.setViewPath("ydksxt/cxbdfx/gtcjxxbdfx.jsp");
    }
    @Transactional
    public void exportGtcjxxbdfxjg(HttpModel httpModel) {
        Dto inDto = httpModel.getInDto();
        Dto params = Dtos.newDto();
        String gtcjxxSql = "select nvl(kz.shxydm, ry.nsrsbh) nsrsbh,\n" +
                "       td.tdzdh,\n" +
                "       ry.nsrmc,\n" +
                "       kz.kzztdjlx_dm,\n" +
                "       td.tdzmj,\n" +
                "       td.tdzyz,\n" +
                "       syq.tdsyq_ysje nynse,\n" +
                "       gt.tdzdh gt_tdzdh,\n" +
                "       gt.tdsyqr gt_tdsyqr,\n" +
                "       gt.tdmj gt_tdmj,\n" +
                "       gt.tdyz gt_tdyz,\n" +
                "       case\n" +
                "         when td.tdzmj = gt.tdmj and td.tdzyz = gt.tdyz then\n" +
                "          '正常'\n" +
                "         else\n" +
                "          '异常'\n" +
                "       end fxjg\n" +
                "  from ydks.dj_tdjcxx    td,\n" +
                "       ydks.dj_tdsyqdjxx syq,\n" +
                "       ydks.dj_ryxx      ry,\n" +
                "       ydks.dj_ryxx_kz   kz,\n" +
                "       ydks.dj_wbdr_gtxx gt\n" +
                " where td.tdbh = syq.tdbh\n" +
                "   and syq.tdsyrlx_dm = '01'\n" +
                "   and syq.djxh = ry.djxh\n" +
                "   and ry.djxh = kz.djxh\n" +
                "   and td.yxbz = 'Y'\n" +
                "   and syq.yxbz = 'Y'\n" +
                "   and td.tdzdh = gt.tdzdh(+)\n" +
                "   and 'Y' = gt.yxbz(+)\n";
        if (!StringUtils.isEmpty(inDto.getString("nsrsbh"))) {
            params.put("nsrsbh", inDto.get("nsrsbh"));
            gtcjxxSql += "   and (ry.nsrsbh = :nsrsbh or kz.shxydm = :nsrsbh)\n";
        }
        if (!StringUtils.isEmpty(inDto.getString("nsrmc"))) {
            params.put("nsrmc", "%" + inDto.get("nsrmc") + "%");
            gtcjxxSql += "   and ry.nsrmc like :nsrmc\n";
        }
        if (!StringUtils.isEmpty(inDto.getString("zgswjgDm"))) {
            params.put("zgswjgDm", inDto.get("zgswjgDm"));
            gtcjxxSql += "   and ry.zgswjg_dm = :zgswjgDm\n";
        }
        if (!StringUtils.isEmpty(inDto.getString("zgswskfjDm"))) {
            params.put("zgswskfjDm", inDto.get("zgswskfjDm"));
            gtcjxxSql += "   and ry.zgswskfj_dm = :zgswskfjDm\n";
        }
        if (!StringUtils.isEmpty(inDto.getString("ssglyDm"))) {
            params.put("ssglyDm", inDto.get("ssglyDm"));
            gtcjxxSql += "   and ry.ssgly_dm = :ssglyDm\n";
        }
        if (!StringUtils.isEmpty(inDto.getString("tdzdh"))) {
            params.put("tdzdh", inDto.get("tdzdh"));
            gtcjxxSql += "   and td.tdzdh = :tdzdh\n";
        }
        if (!StringUtils.isEmpty(inDto.getString("tdbh"))) {
            params.put("tdbh", inDto.get("tdbh"));
            gtcjxxSql += "   and td.tdbh = :tdbh\n";
        }
        if (!StringUtils.isEmpty(inDto.getString("tdzdh"))) {
            params.put("tdzdh", inDto.get("tdzdh"));
            gtcjxxSql += "and td.tdzdh = :tdzdh\n";
        }
        if (!StringUtils.isEmpty(inDto.getString("sqyqsrq"))) {
            params.put("sqyqsrq", inDto.getDate("sqyqsrq"));
            gtcjxxSql += "   and syq.tdsyq_qsrq >= :sqyqsrq\n";
        }
        if (!StringUtils.isEmpty(inDto.getString("syqzzrq"))) {
            params.put("syqzzrq", inDto.getDate("syqzzrq"));
            gtcjxxSql += "   and syq.tdsyq_zzrq <= :syqzzrq";
        }
        List<Map<String, Object>> gtcjxxes = persistenceDAO.findAllBySql(gtcjxxSql, params);
        List<Map<String, Object>> gtcjxxesNew = new ArrayList<>();
        for (Map<String, Object> map : gtcjxxes) {
            //针对个体考虑名称也同时需要相同再进行比对，单位直接以宗地号比对
            if (("1120".equals(map.get("kzztdjlxDm")) && (map.get("nsrmc").equals(map.get("gtTdsyqr")) || map.get("gtTdsyqr") == null)) ||
                    "1110".equals(map.get("kzztdjlxDm"))) {
                gtcjxxesNew.add(map);
            }
        }
        Dto outDto = Dtos.newOutDto();
        if (gtcjxxes.size() == 0) {
            outDto.setAppCode("noXX");
            outDto.setAppMsg("根据所选条件未能获得相关数据!");
        } else {
            try {
                InputStream inputStream = getClass().getResourceAsStream("国土采集信息比对分析模板.xlsx");
                XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
                XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
                for (int i = 0; i < gtcjxxesNew.size(); i++) {
                    XSSFRow xssfRow = xssfSheet.getRow(i + 3);
                    if (xssfRow == null) {
                        xssfRow = xssfSheet.createRow(i + 3);
                        xssfRow.copyRowFrom(xssfSheet.getRow(3),new CellCopyPolicy());
                    }
                    Map map = gtcjxxesNew.get(i);
                    xssfRow.getCell(0).setCellValue(i + 1);
                    xssfRow.getCell(1).setCellValue(String.valueOf(map.get("nsrsbh")).replace("null",""));
                    xssfRow.getCell(2).setCellValue(String.valueOf(map.get("nsrmc")).replace("null",""));
                    xssfRow.getCell(3).setCellValue(String.valueOf(map.get("tdzdh")).replace("null",""));
                    xssfRow.getCell(4).setCellValue(String.valueOf(map.get("tdzmj")).replace("null",""));
                    xssfRow.getCell(5).setCellValue(String.valueOf(map.get("tdzyz")).replace("null",""));
                    xssfRow.getCell(6).setCellValue(String.valueOf(map.get("nynse")).replace("null",""));
                    xssfRow.getCell(7).setCellValue(String.valueOf(map.get("gtTdmj")).replace("null",""));
                    xssfRow.getCell(8).setCellValue(String.valueOf(map.get("gtTdyz")).replace("null",""));
                    xssfRow.getCell(9).setCellValue(String.valueOf(map.get("fxjg")).replace("null",""));
                    XSSFCellStyle xssfCellStyle=xssfWorkbook.createCellStyle();
                    xssfCellStyle.cloneStyleFrom(xssfSheet.getRow(3).getCell(9).getCellStyle());
                    XSSFFont xssfFont=xssfWorkbook.createFont();
                    if ("异常".equals(String.valueOf(map.get("fxjg")).replace("null",""))) {
                        xssfFont.setColor(new XSSFColor(Color.RED));
                    } else {
                        xssfFont.setColor(new XSSFColor(Color.BLACK));
                    }
                    xssfCellStyle.setFont(xssfFont);
                    xssfRow.getCell(9).setCellStyle(xssfCellStyle);
                }
                File file = new File(httpModel.getRequest().getServletContext().getRealPath("download"), 
                		//"bdjg_" + httpModel.getUserModel().getAccount() + "_gtcjxi.xlsx");
                "比对结果_" + httpModel.getUserModel().getAccount() + "_国土采集信息.xlsx");
                if (!file.exists()) {
                    file.createNewFile();
                } else {
                    file.delete();
                    file.createNewFile();
                }
                FileOutputStream fileOutputStream = new FileOutputStream(file);
                xssfWorkbook.write(fileOutputStream);
                fileOutputStream.flush();
                fileOutputStream.close();
                xssfWorkbook.close();
                outDto.setAppCode("suc");
                outDto.put("downloadUrl", "download/" + file.getName());
            } catch (IOException e) {
                outDto.setAppCode("err");
                outDto.setAppMsg("执行比对分析时出现异常：" + e.getMessage());
                logger.error(e);
            }
        }
        httpModel.setOutMsg(AOSJson.toJson(outDto));
    }
    @Transactional
    public void queryGtcjxxbdfxYcByParams(HttpModel httpModel) {
    	Dto inDto = httpModel.getInDto();
        Dto params = Dtos.newDto();
        String fgcjxxSql ="select * from（\n"+
        		"select nvl(kz.shxydm, ry.nsrsbh) nsrsbh,\n" +
                "       td.tdzdh,\n" +
                "       ry.nsrmc,\n" +
                "       kz.kzztdjlx_dm,\n" +
                "       td.tdzmj,\n" +
                "       td.tdzyz,\n" +
                "       syq.tdsyq_ysje nynse,\n" +
                "       gt.tdzdh gt_tdzdh,\n" +
                "       gt.tdsyqr gt_tdsyqr,\n" +
                "       gt.tdmj gt_tdmj,\n" +
                "       gt.tdyz gt_tdyz,\n" +
                "       case\n" +
                "         when td.tdzmj = gt.tdmj and td.tdzyz = gt.tdyz then\n" +
                "          '正常'\n" +
                "         else\n" +
                "          '异常'\n" +
                "       end fxjg\n" +
                "  from ydks.dj_tdjcxx    td,\n" +
                "       ydks.dj_tdsyqdjxx syq,\n" +
                "       ydks.dj_ryxx      ry,\n" +
                "       ydks.dj_ryxx_kz   kz,\n" +
                "       ydks.dj_wbdr_gtxx gt\n" +
                " where td.tdbh = syq.tdbh\n" +
                "   and syq.tdsyrlx_dm = '01'\n" +
                "   and syq.djxh = ry.djxh\n" +
                "   and ry.djxh = kz.djxh\n" +
                "   and td.yxbz = 'Y'\n" +
                "   and syq.yxbz = 'Y'\n" +
                "   and td.tdzdh = gt.tdzdh(+)\n" +
                "   and 'Y' = gt.yxbz(+))t\n"+
                "where t.fxjg='异常'";
        
       // List<Map<String, Object>> fgcjxxes = persistenceDAO.findAllBySql(fgcjxxSql, params);
        List results = persistenceDAO.findAllBySql(fgcjxxSql, params);
        for (int i = 0; i < results.size(); i++) {
            Map map = (Map) results.get(i);
            map.put("lxrdz", getFullAddress((String) map.get("lxrdzXzqhdsDm"), (String) map.get("lxrdzXzqhxqDm"), (String) map.get("lxrdzJdxzDm"), (String) map.get("lxrdzSqjqDm"), (String) map.get("lxrdzDlxdDm"), (String) map.get("lxrdzLh")));
            map.put("zldz", getFullAddress((String) map.get("xzqhdszDm"), (String) map.get("xzqhxqDm"), (String) map.get("jdxzDm"), (String) map.get("zldzSqjqDm"), (String) map.get("zldzDlxdDm"), (String) map.get("zldzLh")));
        }
        httpModel.setOutMsg(AOSJson.toGridJson(AOSUtils.list2Map(results)));
    }
    @Transactional
    public void queryGtcjxxbdfxZcByParams(HttpModel httpModel) {
    	Dto inDto = httpModel.getInDto();
        Dto params = Dtos.newDto();
        String fgcjxxSql ="select * from（\n"+
        		"select nvl(kz.shxydm, ry.nsrsbh) nsrsbh,\n" +
                "       td.tdzdh,\n" +
                "       ry.nsrmc,\n" +
                "       kz.kzztdjlx_dm,\n" +
                "       td.tdzmj,\n" +
                "       td.tdzyz,\n" +
                "       syq.tdsyq_ysje nynse,\n" +
                "       gt.tdzdh gt_tdzdh,\n" +
                "       gt.tdsyqr gt_tdsyqr,\n" +
                "       gt.tdmj gt_tdmj,\n" +
                "       gt.tdyz gt_tdyz,\n" +
                "       case\n" +
                "         when td.tdzmj = gt.tdmj and td.tdzyz = gt.tdyz then\n" +
                "          '正常'\n" +
                "         else\n" +
                "          '异常'\n" +
                "       end fxjg\n" +
                "  from ydks.dj_tdjcxx    td,\n" +
                "       ydks.dj_tdsyqdjxx syq,\n" +
                "       ydks.dj_ryxx      ry,\n" +
                "       ydks.dj_ryxx_kz   kz,\n" +
                "       ydks.dj_wbdr_gtxx gt\n" +
                " where td.tdbh = syq.tdbh\n" +
                "   and syq.tdsyrlx_dm = '01'\n" +
                "   and syq.djxh = ry.djxh\n" +
                "   and ry.djxh = kz.djxh\n" +
                "   and td.yxbz = 'Y'\n" +
                "   and syq.yxbz = 'Y'\n" +
                "   and td.tdzdh = gt.tdzdh(+)\n" +
                "   and 'Y' = gt.yxbz(+))t\n"+
                "where t.fxjg='正常'";
        
       // List<Map<String, Object>> fgcjxxes = persistenceDAO.findAllBySql(fgcjxxSql, params);
        List results = persistenceDAO.findAllBySql(fgcjxxSql, params);
        for (int i = 0; i < results.size(); i++) {
            Map map = (Map) results.get(i);
            map.put("lxrdz", getFullAddress((String) map.get("lxrdzXzqhdsDm"), (String) map.get("lxrdzXzqhxqDm"), (String) map.get("lxrdzJdxzDm"), (String) map.get("lxrdzSqjqDm"), (String) map.get("lxrdzDlxdDm"), (String) map.get("lxrdzLh")));
            map.put("zldz", getFullAddress((String) map.get("xzqhdszDm"), (String) map.get("xzqhxqDm"), (String) map.get("jdxzDm"), (String) map.get("zldzSqjqDm"), (String) map.get("zldzDlxdDm"), (String) map.get("zldzLh")));
        }
        httpModel.setOutMsg(AOSJson.toGridJson(AOSUtils.list2Map(results)));
    }
}
